package com.niu.wm.service.impl;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.niu.wm.service.BaseService;
import com.niu.wm.service.ProductService;
import com.niu.wm.core.error.BizException;
import com.niu.wm.core.error.GlobalErrorCode;
import com.niu.wm.dao.mapper.MaxidMapper;
import com.niu.wm.dao.mapper.ProductCategoryMapper;
import com.niu.wm.dao.mapper.ProductMapper;
import com.niu.wm.model.Maxid;
import com.niu.wm.model.Product;
import com.niu.wm.model.ProductCategory;
import com.niu.wm.model.User;
import com.niu.wm.form.ProductSearchForm;
import com.niu.wm.vo.ProductVO;

@Service
public class ProductServiceImpl extends BaseService implements ProductService{

    @Autowired
    private ProductMapper  productMapper;
    @Autowired
    private ProductCategoryMapper categoryMapper;
    @Autowired
    private MaxidMapper maxidMapper;
    @Autowired
    private OssServiceImpl ossService;
    
    private int IMG_MAX_WIDTH = 160;
	
	public Product load(Long id){
		return productMapper.selectByPrimaryKey(id);
	}
	
	@Transactional
    @Override
	public int insert(User user, Product product){
		if( StringUtils.isEmpty(product.getDesc_en()))
			throw new BizException(GlobalErrorCode.INVALID_ARGUMENT, "产品名称不能为空");	
		if( product.getCatId() == null)
			throw new BizException(GlobalErrorCode.INVALID_ARGUMENT, "产品分类不能为空");	
		
		product.setCreatorId(user.getId());    	
    	int count = productMapper.insert(product);
    	
    	return count;
	}
    
    @Transactional
    @Override
	public int update(User user, Product product){    	
    	return productMapper.update(product);
	}
    
    @Transactional
    @Override
	public int delete(User user, Long id) {
		int count = 0;
		productMapper.delete(id);

		return count;
	}
    
    @Override
	public int countProduct(ProductSearchForm form){
    	return productMapper.countProduct(form);
    }

    @Override
	public List<ProductVO> queryProduct(ProductSearchForm form, Pageable pageable){
    	List<ProductVO> ls = productMapper.queryProduct(form, pageable);
    	
    	return ls;
    }
    
    @Override
	public int importProduct(User user, String webRootPath, MultipartFile file) throws IOException{
    	Workbook wb = null;
    	Sheet sheet = null;
    	Row captionRow=null, row = null;
    	int count =0;
    	
    	String fileNameLower = file.getName().toLowerCase();
    	

    	if( fileNameLower.endsWith(".xlsx"))
    		wb = new XSSFWorkbook( file.getInputStream() );
    	else
    		wb = new HSSFWorkbook( file.getInputStream() );
    	
    	try{
    		sheet = wb.getSheetAt(0);
    		int lastRowNum = sheet.getLastRowNum();
    		if(lastRowNum <1)
    			return count;
    			
	    	captionRow = sheet.getRow(1);
	    	row = captionRow;
	    	  
	    	for(int i=1; i <= lastRowNum; i++){
	    		processRow(user, webRootPath, sheet.getRow(i));
	    		count ++;
	    	}
	    }catch(Exception e){
	    	getLogger().error("", e);
	    	throw new BizException(GlobalErrorCode.INTERNAL_ERROR, "导入异常");
	    }finally{
	    	if( wb !=null)
	    		wb.close();
	    }
    	
    	return count;
    }
    
    private void processRow(User user, String webRootPath,  Row row){
    	Product product = new Product();
    	Cell cell = null;
    	
    	String imageName = null, catCode = null;
    	for(int i=0; i < row.getLastCellNum(); i++){
    		cell = row.getCell(i);
    		if(cell ==null)
    			continue;
    		
    		switch(i){
    		case 0:
    			imageName = cell.getStringCellValue();
    			product.setImageName(imageName);
    			break;
    		case 1:
    			product.setDesc_cn( cell.getStringCellValue() );
    			break;
    		case 2:
    			product.setDesc_en( cell.getStringCellValue() );
    			break;
    		case 3:
    			product.setFeatures( cell.getStringCellValue() );
    			break;
    		case 4:    			
    			catCode = cell.getStringCellValue();
    			ProductCategory cat = categoryMapper.selectByCode(catCode);
    			if(cat !=null)
    				product.setCatId(cat.getId());
    			break;
    		case 5:
    			//产品尺寸
    			if( cell.getCellTypeEnum() == CellType.STRING){
    				product.setSize( cell.getStringCellValue() );
    			}
    			break;
    		case 6:
    			if( cell.getCellTypeEnum() == CellType.STRING){
    				product.setWeight( cell.getStringCellValue() );
    			}else{
    				double dWeight = cell.getNumericCellValue();
    				product.setWeight( Double.toString(dWeight)); 
    			}
    			
    			break;
    		case 7:
    			if( cell.getCellTypeEnum() == CellType.STRING){
    				//product.setMinBookAmount( cell.getStringCellValue() );
    			}else{
    				BigDecimal minAmount = new BigDecimal(cell.getNumericCellValue());
    				product.setMinBookAmount( minAmount.intValue() );
    			}
    			break;
    		case 8:
    			//交货期
    			if( cell.getCellTypeEnum() == CellType.STRING){
    				product.setDeliveryTime( cell.getStringCellValue() );
    			}else{
    				BigDecimal deliveryTime = new BigDecimal(cell.getNumericCellValue());
    				product.setDeliveryTime( String.valueOf(deliveryTime.longValue()) );
    			}
    			break;
    		case 9:
    			product.setSupplierName( cell.getStringCellValue() );
    			break;
    		case 10:
    			if( cell.getCellTypeEnum() == CellType.NUMERIC){
    				BigDecimal price = new BigDecimal(cell.getNumericCellValue());
    				product.setFactoryPrice( price );
    			}
    			break;
    		case 11:
    			//包装箱尺寸	
    			product.setPackageSize( cell.getStringCellValue() );

    			break;
    		case 12:
    			//包装数量	
    			if( cell.getCellTypeEnum() == CellType.STRING){
    				String s = cell.getStringCellValue();
    			}else if( cell.getCellTypeEnum() == CellType.NUMERIC){
    				BigDecimal pkgAmount = new BigDecimal(cell.getNumericCellValue());
    				product.setPackageAmount( pkgAmount.longValue() );
    			}
    			//
    			break;
    		case 13:
    			//包装箱重量
    			if( cell.getCellTypeEnum() == CellType.STRING)
    				product.setPackageWeight( cell.getStringCellValue() );
    			else{
    				//product.setPackageWeight( cell.getNumericCellValue() );
    			}
    			break;
    		case 14:
    			//图片来源	
    			if( cell.getCellTypeEnum() == CellType.STRING)
    				product.setImageSource( cell.getStringCellValue() );
    			
    			break;
    		case 15:
    			//备注
    			product.setMemo(cell.getStringCellValue() );
    			break;
    		}
    	}
    	if( StringUtils.isNotEmpty(product.getDesc_en() )){
    		
    		product.setCreatorId(user.getId());
        	product.setNo( createNo() );
        	

    		//处理图片 必须在产生编号之后
    		processImages(product, webRootPath);    		
    		
        	productMapper.insert(product);
    	}
    }
    
    private void processImages(Product product, String webRootPath){
    	File dirFile = new File( getUploadedImagePath(webRootPath) );
    	
		if( !dirFile.exists())
			return;
		
		
		for(File f:dirFile.listFiles()){
			if(f.isDirectory())
				continue;
			
			String fileName = null, newFileName = null;
			fileName = f.getName();
			int idx = fileName.indexOf(".");
			if(idx <0)
				continue;
			
			fileName = fileName.substring(0, idx);
				
			if (fileName.equals(product.getImageName())
					|| fileName.equals(product.getImageName() + "-1")) {
				newFileName = product.getNo() + "-1"
						+ f.getName().substring(idx);
				product.setImage1(newFileName);
			} else if (fileName.equals(product.getImageName() + "-2")) {
				newFileName = product.getNo() + "-2"
						+ f.getName().substring(idx);
				product.setImage2(newFileName);
			} else if (fileName.equals(product.getImageName() + "-3")) {
				newFileName = product.getNo() + "-3"
						+ f.getName().substring(idx);
				product.setImage3(newFileName);
			} else
				continue;
			
			try{
				ossService.putObject(f, newFileName);
				f.delete();
			}catch(Exception e){
				getLogger().error("", e);
			}
		}
    }
    
    private String createNo(){
    	int iYear =2018;
    	String idCode = "PRODUCT_NO";
		Maxid maxid = maxidMapper.select(idCode, iYear);
		if(maxid == null){
			maxid = new Maxid();
			maxid.setIdCode(idCode);
			maxid.setDay(iYear);
			maxid.setMaxid(1L);
			maxidMapper.insert(maxid);
		}else{
			maxid.setMaxid(maxid.getMaxid() +1L);
			maxidMapper.update(maxid);
		}
		java.text.DecimalFormat df = new java.text.DecimalFormat("000000");
		
		return df.format(maxid.getMaxid());
	}
    
    @Override
    public Workbook exportProducts(ProductSearchForm form) throws IOException{
    	Pageable pager = null;
		
		//fld-code-->fld-title
		List<String[]> lsFld = new ArrayList<String[]>();
		//List< Map(fld-code, fld-value)>
		List<Map<String,Object>> lsRows = new ArrayList<Map<String,Object>>();

		lsFld.add( new String[]{"image_1","PICTURE-1"} );
		lsFld.add( new String[]{"image_2","PICTURE-2"} );
		lsFld.add( new String[]{"image_3","PICTURE-3"} );
		lsFld.add( new String[]{"no","ITEM NO"} );
		lsFld.add( new String[]{"desc_en", "Descr."} );
		lsFld.add( new String[]{"features", "Key specification"} );
		lsFld.add( new String[]{"size", "Size"} );
		lsFld.add( new String[]{"weight", "FOB SHA(USD)"} );
		lsFld.add( new String[]{"min_book_amount", "MOQ"} );
		lsFld.add( new String[]{"package_size", "CTNSiZe"} );
		lsFld.add( new String[]{"package_amount", "QTY/CTN"} );
		lsFld.add( new String[]{"supplier_weight", "Weight/CTN"} );
		lsFld.add( new String[]{"factory_price", "工厂价格"} );
		
		pager = new PageRequest(0, 50000);
		
		List<ProductVO> ls = queryProduct(form, pager);
		
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		// 创建Excel工作簿的第一个Sheet页
		sheet = workbook.createSheet("sheet1");
		
		int rowNo =0;
		row = sheet.createRow(rowNo ++); 

		for(int i=0; i < lsFld.size(); i++){
			String[] pair = lsFld.get(i);
			
			cell = row.createCell(i); 
			cell.setCellType(CellType.STRING);
			cell.setCellValue(new HSSFRichTextString(pair[1]));
			
			sheet.setColumnWidth(i, (int)(IMG_MAX_WIDTH*37.2));
		}
		
		for(ProductVO vo:ls){
			Map<String,Object> mapVal = new HashMap<String,Object>();
			lsRows.add(mapVal);
					
			mapVal.put("image_1", vo.getImage1() );
			mapVal.put("image_2", vo.getImage2() );
			mapVal.put("image_3", vo.getImage3() );
			mapVal.put("no", vo.getNo() );
			mapVal.put("desc_cn", vo.getDesc_cn());
			mapVal.put("desc_en", vo.getDesc_en());
			mapVal.put("features", vo.getFeatures());
			mapVal.put("cat_id", vo.getCatId());
			
			mapVal.put("size", vo.getSize() );
			mapVal.put("weight", vo.getWeight());
			mapVal.put("min_book_amount", vo.getMinBookAmount() );
			mapVal.put("delivery_time", vo.getDeliveryTime() );
			mapVal.put("supplier_name", vo.getSupplierName() );
			mapVal.put("factory_price", vo.getFactoryPrice() );
			
			mapVal.put("package_size", vo.getPackageSize());
			mapVal.put("package_amount", vo.getPackageAmount());
			mapVal.put("package_weight", vo.getPackageWeight());
			mapVal.put("image_source", vo.getImageSource());
			mapVal.put("memo", vo.getMemo());
		}
		

		//靠右对齐
        CellStyle cellRightStyle = workbook.createCellStyle();
        cellRightStyle.setAlignment(HorizontalAlignment.RIGHT);
        cellRightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        
        
		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
		for(Map<String,Object> rowVal: lsRows){
			row = sheet.createRow(rowNo ++);
			
			row.setHeightInPoints( IMG_MAX_WIDTH*0.76f );
			
			for(int i=0; i < lsFld.size(); i++){
				String fldCode = lsFld.get(i)[0];	
				
				if(fldCode.equals("image_1") || fldCode.equals("image_2") || fldCode.equals("image_3")){
					cell = createCell(workbook, row, i, HorizontalAlignment.LEFT, VerticalAlignment.TOP); 
					createImage2Cell(workbook, cell, (String)rowVal.get(fldCode) );
					continue;
				}else{
					cell = createCell(workbook, row, i, HorizontalAlignment.LEFT, VerticalAlignment.CENTER);
				}
				
				Object valueObj = rowVal.get(fldCode);
				if(valueObj instanceof Integer){
					Integer value = (Integer)valueObj;
					cell.setCellStyle(cellRightStyle);
					cell.setCellType(CellType.NUMERIC);
					if(value != null)
						cell.setCellValue(value.intValue());
				} else if(valueObj instanceof Long){
					Long value = (Long)valueObj;
					cell.setCellStyle(cellRightStyle);
					cell.setCellType(CellType.NUMERIC);
					if(value != null)
						cell.setCellValue(value.longValue());
				} else if(valueObj instanceof BigDecimal){
					BigDecimal value = (BigDecimal)valueObj;
					cell.setCellStyle(cellRightStyle);
					cell.setCellType(CellType.NUMERIC);
					if(value != null)
						cell.setCellValue(value.doubleValue());
				} else if(valueObj instanceof java.util.Date){
					java.util.Date value = (java.util.Date)valueObj;
					cell.setCellType(CellType.STRING);
					if(value != null)
						cell.setCellValue(sdf.format(value));
				}else{
					cell.setCellType(CellType.STRING);
					if(valueObj == null)
						cell.setCellValue(new HSSFRichTextString(""));
					else
						cell.setCellValue(new HSSFRichTextString(valueObj.toString()));
				}
			}
		}		
		
		return workbook;
    }
	
	private void createImage2Cell(Workbook workbook, Cell cell, String fileName) throws IOException{
		String fileExt = null;
		if(StringUtils.isNotEmpty(fileName)){
			int idx = fileName.lastIndexOf(".");
			if(idx >=0)
				fileExt = fileName.substring(idx +1).toLowerCase();
		}
		if(StringUtils.isEmpty(fileExt))
			return;
		
		Sheet sheet = null;
		sheet = cell.getSheet();
		
		int picture_format = Workbook.PICTURE_TYPE_JPEG;
		
		
		if(fileExt.equals("jpg") || fileExt.equals("jpeg"))
			picture_format = Workbook.PICTURE_TYPE_JPEG;
		else if(fileExt.equals("png"))
			picture_format = Workbook.PICTURE_TYPE_PNG;
		else if(fileExt.equals("dib"))
			picture_format = Workbook.PICTURE_TYPE_DIB;
		else
			return;
		
		StringBuffer srcImage = new StringBuffer("http://");
		srcImage.append(ossService.getBucketName()).append(".");
		srcImage.append(ossService.getEndpoint()).append("/").append(fileName);
		
		String imageUrl = null;
		/*
		int imgWidth = 0, imgHeight=0;
		String imgInfoUrl = srcImage.toString() + "?x-oss-process=image/info";
		{
			java.net.URL url = new java.net.URL(imgInfoUrl);
			InputStream is = url.openStream();
		    byte[] bytes = IOUtils.toByteArray(is);
		    String s = new String(bytes);
		    JSONObject json = JSON.parseObject(s);
		    imgWidth  = json.getJSONObject("ImageWidth").getIntValue("value");
		    imgHeight = json.getJSONObject("ImageHeight").getIntValue("value");
		    is.close();
		}
		if(imgWidth  > imgHeight){
			imgHeight = (int) Math.round( (imgHeight *IMG_MAX_WIDTH*1.0)/(imgWidth *1.0) );
			imgWidth = IMG_MAX_WIDTH;
		}else{
			imgWidth = (int) Math.round( (imgWidth *IMG_MAX_WIDTH*1.0)/(imgHeight *1.0) );
			imgHeight = IMG_MAX_WIDTH;
		}
				
		imageUrl = srcImage.toString() + "?x-oss-process=image/resize,h_" + imgHeight;
		*/
		imageUrl = srcImage.toString()
				 + "?x-oss-process=image/resize,m_lfit,limit_0,"
				 + "h_" + IMG_MAX_WIDTH + ",w_" + IMG_MAX_WIDTH;
		
		java.net.URL url = new java.net.URL(imageUrl);		
		
		//add picture data to this workbook.
		
	    InputStream is = url.openStream();
	    byte[] bytes = IOUtils.toByteArray(is);
	    int pictureIdx = workbook.addPicture(bytes, picture_format);
	    is.close();

	    CreationHelper helper = workbook.getCreationHelper();
	    
	 // Create the drawing patriarch.  This is the top level container for all shapes. 
	    Drawing drawing = sheet.createDrawingPatriarch();

	    //add a picture shape
	    ClientAnchor anchor = helper.createClientAnchor();

	    //set top-left corner of the picture,
	    //subsequent call of Picture#resize() will operate relative to it
	    anchor.setCol1( cell.getColumnIndex() );
	    anchor.setRow1( cell.getRowIndex() );
	    anchor.setDx1(1);
	    anchor.setDy1(1);
	    /*
	    anchor.setCol2( cell.getColumnIndex() );
	    anchor.setRow2( cell.getRowIndex() );
	    anchor.setDx2(imgWidth*2);
	    anchor.setDy2(imgHeight*2);
	    */
	    Picture pict = drawing.createPicture(anchor, pictureIdx);
	    pict.resize();
	}
	
    
    @Override
    public String getUploadedImagePath(String webRootPath){
    	String filePath = webRootPath + "/images/product/uploaded";
    	File f = new File(filePath);
    	if(!f.exists())
    		f.mkdirs();
    	
    	return filePath;
    }
    
    private Cell createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
        Cell cell = row.createCell(column);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
        
        return cell;
    }
    
    private static void test() throws IOException{
    	String fileName = "f:/AAA-3.jpg";
		String fileExt = null;
		if(StringUtils.isNotEmpty(fileName)){
			int idx = fileName.lastIndexOf(".");
			if(idx >=0)
				fileExt = fileName.substring(idx +1).toLowerCase();
		}
		if(StringUtils.isEmpty(fileExt))
			return;
		
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		// 创建Excel工作簿的第一个Sheet页
		sheet = workbook.createSheet("sheet1");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		
		row.setHeight((short)10000);
		sheet.setColumnWidth(0, 30000);
		
		int picture_format = Workbook.PICTURE_TYPE_JPEG;
		
		
		if(fileExt.equals("jpg") || fileExt.equals("jpeg"))
			picture_format = Workbook.PICTURE_TYPE_JPEG;
		else if(fileExt.equals("png"))
			picture_format = Workbook.PICTURE_TYPE_PNG;
		else if(fileExt.equals("dib"))
			picture_format = Workbook.PICTURE_TYPE_DIB;
		else
			return;
		
		
	    InputStream is = new java.io.FileInputStream(fileName);
	    byte[] bytes = IOUtils.toByteArray(is);
	    int pictureIdx = workbook.addPicture(bytes, picture_format);
	    is.close();

	    CreationHelper helper = workbook.getCreationHelper();
	    
	 // Create the drawing patriarch.  This is the top level container for all shapes. 
	    Drawing drawing = sheet.createDrawingPatriarch();

	    //add a picture shape
	    ClientAnchor anchor = helper.createClientAnchor();

	    //set top-left corner of the picture,
	    //subsequent call of Picture#resize() will operate relative to it
	    anchor.setCol1( cell.getColumnIndex() );
	    anchor.setRow1( cell.getRowIndex() );
	   
	    anchor.setCol2( cell.getColumnIndex() );
	    anchor.setRow2( cell.getRowIndex() );
	    anchor.setDx1(0);
	    anchor.setDy1(0);
	    anchor.setDx2(300);
	    anchor.setDy2(112);
	    
	    Picture pict = drawing.createPicture(anchor, pictureIdx);
	    //pict.resize();
	    
	    OutputStream fos = new java.io.FileOutputStream("f:/test" + System.currentTimeMillis() + ".xls");
	    workbook.write(fos);
	    fos.close();
	    workbook.close();
	}
    
    public static void main(String... args){
    	try{
    		test();
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    }
}
